from sqlalchemy import UniqueConstraint, PrimaryKeyConstraint, Index, Sequence
from sqlalchemy.dialects.postgresql import TIMESTAMP

from tzq.extensions import db

"""
    数据库比对系统，公共类。
"""


class TableInfo(db.Model):
    """ 表及字段信息表 """
    __tablename__ = 'dc_table_info_t'  # 数据库表名
    __table_args__ = (
        # 主键约束
        PrimaryKeyConstraint('table_info_id', name='dc_table_info_pk', comment='主键约束'),
        # 唯一约束， SQLAlchemy 实现联合唯一约束
        UniqueConstraint('source', 'schema', 'table_name', 'column_name', name='uc_dc_table_info_2',
                         comment='唯一约束：根据source(来源)、table_name(表名)、column_name(字段名)、schema来进行唯一约束。'),
        # 唯一索引
        Index('uidx_dc_table_info_1', 'source', 'schema', 'table_name', 'column_name', unique=True),
        # 表注释
        {'comment': '表及字段信息表'},
    )
    table_info_id = db.Column(
        db.BigInteger,  # 字段类型
        Sequence('dc_table_info_s'),  # 序列名称
        primary_key=True,  # 主键
        server_default=Sequence('dc_table_info_s').next_value(),  # 默认值
        nullable=False,  # 非空约束。False为禁止为空
        comment='主键ID'
    )
    source = db.Column(db.String(500), nullable=False, comment='来源')
    database_type = db.Column(db.String(500), nullable=False, comment='数据库类型')
    schema = db.Column(db.String(500), nullable=False, comment='schema')
    owner = db.Column(db.String(500), nullable=False, comment='owner。表的属主')
    table_name = db.Column(db.String(500), nullable=False, comment='表名')
    table_comment = db.Column(db.String(4000), comment='表注释')
    column_num = db.Column(db.BigInteger, nullable=False, comment='字段序号')
    column_name = db.Column(db.String(500), nullable=False, comment='列名')
    data_type = db.Column(db.String(500), nullable=False, comment='数据类型')
    data_length = db.Column(db.String(500), comment='字段长度')
    data_default = db.Column(db.String(500), comment='字段默认值')
    nullable = db.Column(db.String(500), nullable=False, comment='非空约束。是：“Y”；否：“N”。')
    pkey = db.Column(db.String(500), comment='是否是主键。是：“Y”；否：“N”。')
    column_comment = db.Column(db.String(4000), comment='字段注释')
    # creation_date = db.Column(db.DateTime, nullable=True, server_default=func.now(), comment='创建时间')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')

    def __repr__(self):
        return '<TableInfo %r>' % self.table_info_id

    def to_dict(self):
        return {
            'table_info_id': self.table_info_id,
            'source': self.source,
            'database_type': self.database_type,
            'schema': self.schema,
            'owner': self.owner,
            'table_name': self.table_name,
            'table_comment': self.table_comment,
            'column_num': self.column_num,
            'column_name': self.column_name,
            'data_type': self.data_type,
            'data_length': self.data_length,
            'data_default': self.data_default,
            'nullable': self.nullable,
            'pkey': self.pkey,
            'column_comment': self.column_comment,
            'creation_date': self.creation_date.strftime('%Y-%m-%d %H:%M:%S'),
        }


class IndexInfo(db.Model):
    """ 索引信息表 """
    __tablename__ = 'dc_index_info_t'
    __table_args__ = (
        PrimaryKeyConstraint('index_info_id', name='dc_index_info_pk', comment='主键'),
        Index('uidx_dc_index_info_1', 'source', 'table_name', 'index_name', 'schema', unique=True),
        {'comment': '索引信息表', },
    )
    index_info_id = db.Column(
        db.BigInteger,
        Sequence('dc_index_info_s'),
        primary_key=True,
        server_default=Sequence('dc_index_info_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    source = db.Column(db.String(500), nullable=False, comment='来源')
    schema = db.Column(db.String(255), nullable=False, comment='schema')
    owner = db.Column(db.String(255), nullable=False, comment='owner。索引的属主')
    table_name = db.Column(db.String(64), nullable=False, comment='表名')
    index_name = db.Column(db.String(64), nullable=False, comment='索引名')
    index_def = db.Column(db.String(4000), nullable=False, comment='索引定义')
    index_is_primary = db.Column(db.String(1), nullable=False, comment='是否为主键。是：“Y”；否：“N”。')
    index_is_unique = db.Column(db.String(1), nullable=False, comment='是否为唯一索引。是：“Y”；否：“N”。')
    index_columns = db.Column(db.String(3000), nullable=False, comment='索引涉及的字段集')
    where_condition = db.Column(db.String(2000), comment='索引是否有where条件内容。有就显示where条件的内容。没有就为空。')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')

    def to_dict(self):
        return {
            'index_info_id': self.index_info_id,
            'source': self.source,
            'schema': self.schema,
            'owner': self.owner,
            'table_name': self.table_name,
            'index_name': self.index_name,
            'index_def': self.index_def,
            'index_is_primary': self.index_is_primary,
            'index_is_unique': self.index_is_unique,
            'index_columns': self.index_columns,
            'where_condition': self.where_condition,
            'creation_date': self.creation_date.strftime('%Y-%m-%d %H:%M:%S'),
        }


class SequenceInfo(db.Model):
    """ 序列信息表 """
    __tablename__ = 'dc_sequence_info_t'
    __table_args__ = (
        PrimaryKeyConstraint('sequence_info_id', name='dc_sequence_info_pk', comment='主键'),
        Index('uidx_dc_sequence_info_1', 'source', 'schema', 'sequence_name', unique=True),
        {'comment': '序列信息表', },
    )
    sequence_info_id = db.Column(
        db.BigInteger,
        Sequence('dc_sequence_info_s'),
        primary_key=True,
        server_default=Sequence('dc_sequence_info_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    source = db.Column(db.String(500), nullable=False, comment='来源')
    schema = db.Column(db.String(255), nullable=False, comment='schema')
    owner = db.Column(db.String(255), comment='owner。序列的属主')
    sequence_name = db.Column(db.String(64), nullable=False, comment='序列名称')
    min_value = db.Column(db.BigInteger, comment='序列的最小值')
    max_value = db.Column(db.BigInteger, comment='序列的最大值')
    increment_by = db.Column(db.BigInteger, comment='每次序列递增值')
    cache_size = db.Column(db.BigInteger, comment='序列的缓存大小')
    cycle_flag = db.Column(db.String(64), comment='序列的循环标志')
    last_number = db.Column(db.BigInteger, comment='序列当前值')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')


class ProcInfo(db.Model):
    """ 函数信息表 """
    __tablename__ = 'dc_proc_info_t'
    __table_args__ = (
        PrimaryKeyConstraint('proc_info_id', name='dc_proc_info_pk', comment='主键'),
        Index('uidx_dc_proc_info_1', 'source', 'schema', 'pro_name', 'pro_arg', unique=True),
        {'comment': '函数信息表', },
    )
    proc_info_id = db.Column(
        db.BigInteger,
        Sequence('dc_proc_info_s'),
        primary_key=True,
        server_default=Sequence('dc_proc_info_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    source = db.Column(db.String(500), nullable=False, comment='来源')
    schema = db.Column(db.String(500), nullable=False, comment='schema')
    owner = db.Column(db.String, comment='owner。序列的属主')
    pro_name = db.Column(db.String(500), nullable=False, comment='函数名')
    pro_arg = db.Column(db.String(5000), comment='函数参数集')
    pro_arg_type = db.Column(db.String(5000), comment='函数输入输出的参数类型')
    pro_ret_type = db.Column(db.String(5000), comment='函数返回值的参数类型')
    prosrc = db.Column(db.Text, comment='函数源码')
    pro_content = db.Column(db.Text, comment='函数内容')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')


class PrivilegeInfo(db.Model):
    """ 权限信息表 """
    __tablename__ = 'dc_privilege_info_t'
    __table_args__ = (
        PrimaryKeyConstraint('privilege_info_id', name='dc_privilege_info_pk', comment='主键'),
        Index('uidx_dc_privilege_info_1',
              'source', 'schema', 'object_name', 'type', 'grantor', 'grantee', 'privilege',
              unique=True),
        {'comment': '权限信息表', },
    )
    privilege_info_id = db.Column(
        db.BigInteger,
        Sequence('dc_privilege_info_s'),
        primary_key=True,
        server_default=Sequence('dc_privilege_info_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    source = db.Column(db.String, nullable=False, comment='来源')
    schema = db.Column(db.String, nullable=False, comment='schema')
    owner = db.Column(db.String, nullable=False, comment='owner。对象的属主')
    object_name = db.Column(db.String, nullable=False, comment='对象名称')
    type = db.Column(db.String, nullable=False, comment='对象类型')
    grantor = db.Column(db.String, nullable=False, comment='授权者')
    grantee = db.Column(db.String, nullable=False, comment='授予者')
    privilege = db.Column(db.String, nullable=False, comment='权限。增删改查truncate等。')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='创建时间')


class TableDefine(db.Model):
    """ 记录表结构ddl脚本表 """
    __tablename__ = 'dc_table_define_t'
    __table_args__ = (
        PrimaryKeyConstraint('table_define_id', name='dc_table_define_pk', comment='主键'),
        Index('uidx_dc_table_define_1',
              'source', 'schema', 'table_name',
              unique=True),
        {'comment': '记录表结构ddl脚本表', },
    )
    table_define_id = db.Column(
        db.BigInteger,
        Sequence('dc_table_define_s'),
        primary_key=True,
        server_default=Sequence('dc_table_define_s').next_value(),
        nullable=False,
        comment='主键ID'
    )
    source = db.Column(db.String, nullable=False, comment='来源')
    schema = db.Column(db.String, nullable=False, comment='schema')
    owner = db.Column(db.String, comment='owner。对象的属主')
    table_name = db.Column(db.String, nullable=False, comment='表名')
    table_define = db.Column(db.String, nullable=False, comment='表定义ddl脚本')
    table_creator = db.Column(db.String, comment='表的创建者')
    created = db.Column(TIMESTAMP(timezone=False, precision=0), comment='源端表的创建时间')
    last_ddl_time = db.Column(TIMESTAMP(timezone=False, precision=0), comment='源端表的最后ddl时间')
    creation_date = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                              server_default=db.func.clock_timestamp(), comment='落到比对系统数据的创建时间')

    def to_dict(self):
        return {
            'table_define_id': self.table_define_id,
            'source': self.source,
            'schema': self.schema,
            'owner': self.owner,
            'table_name': self.table_name,
            'table_define': self.table_define,
            'table_creator': self.table_creator,
            'created': self.created.strftime('%Y-%m-%d %H:%M:%S'),
            'last_ddl_time': self.last_ddl_time.strftime('%Y-%m-%d %H:%M:%S'),
            'creation_date': self.creation_date.strftime('%Y-%m-%d %H:%M:%S'),
        }
